package data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import logic.Activity;
import logic.Product;

public class JDBCActivityDAO implements IActivityDAO {

	/* falta hacer este metodo */
	public List<Activity> selectFavouriteActivities(String userId) {
		
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        ResultSet result = null;
        List<Activity> l = new LinkedList<Activity>();
        List<String> la = new LinkedList<String>();
        List<String> la2 = new LinkedList<String>();
        String sql = "SELECT * FROM volunterrhistory WHERE iduser = ?";
        String sql2 = "SELECT * FROM activities WHERE id = idactivity GROUP BY type ORDER BY count( * ) DESC";

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, userId);
            result = stmt.executeQuery();

            while (result.next()) {
    
                la.add(result.getString("idactivity"));   
            }
            for(String s: la){
            	
            	stmt = conn.prepareStatement(sql2);
            	stmt.setString(1, s);
            	result = stmt.executeQuery();
            	
            	while(result.next()){
            		
            		la2.add(result.getString("type"));
            	}
            	           
            
            }
            
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
        	ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null) {
                    result.close();
                }

                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }

        return l;
    }

	public List<Activity> selectMostPopularActivities() {
		    Connection conn = ConnectionManager.getInstance().checkOut();
			PreparedStatement stmt = null;
	        ResultSet result = null;
	        List<Activity> lo = new LinkedList<Activity>();
	        String sql = "SELECT * FROM activities ORDER BY signedVolunteers DESC LIMIT 5";

	        try {
	            stmt = conn.prepareStatement(sql);
	            result = stmt.executeQuery();

	            while (result.next()) {
	            	Activity a = new Activity();
	            	 a.setId(result.getString("id"));
	                 a.setDescription(result.getString("description"));
	                 a.setIddestination(result.getString("iddestination"));
	                 a.setMaxnumvolunteers(result.getInt("maxnumvolunteers"));
	                 a.setMinnumvolunteers(result.getInt("minnumvolunteers"));
	                 a.setName(result.getString("name"));
	                 a.setOrganization(result.getString("organization"));
	                 a.setSignedVolunteers(result.getInt("subscribevolunteers"));
	                 a.setType(result.getString("type"));

	                lo.add(a);
	                
	            }
	        } catch (SQLException e) {
	            System.out.println("Message: " + e.getMessage());
	            System.out.println("SQLState: " + e.getSQLState());
	            System.out.println("ErrorCode: " + e.getErrorCode());
	        } finally {
	        	ConnectionManager.getInstance().checkIn(conn);
	            try {
	                if (result != null) {
	                    result.close();
	                }

	                if (stmt != null) {
	                    stmt.close();
	                }
	            } catch (SQLException e) {
	            }
	        }

	        return lo;
	    }
	

	public List<Activity> selectAll() {
		 Connection conn = ConnectionManager.getInstance().checkOut();
		 PreparedStatement stmt = null;
	     ResultSet result = null;
         List<Activity> lo = new LinkedList<Activity>();
	     String sql = "SELECT * FROM activities";

	        try {
	            stmt = conn.prepareStatement(sql);
	            result = stmt.executeQuery();

	            while (result.next()) {
	                Activity a = new Activity();
	                a.setId(result.getString("id"));
	                a.setDescription(result.getString("description"));
	                a.setIddestination(result.getString("iddestination"));
	                a.setMaxnumvolunteers(result.getInt("maxnumvolunteers"));
	                a.setMinnumvolunteers(result.getInt("minnumvolunteers"));
	                a.setName(result.getString("name"));
	                a.setOrganization(result.getString("organization"));
	                a.setSignedVolunteers(result.getInt("subscribevolunteers"));
	                a.setType(result.getString("type"));   

	                lo.add(a);
	            }
	        } catch (SQLException e) {
	            System.out.println("Message: " + e.getMessage());
	            System.out.println("SQLState: " + e.getSQLState());
	            System.out.println("ErrorCode: " + e.getErrorCode());
	        } finally {
	        	ConnectionManager.getInstance().checkIn(conn);
	            try {
	                if (result != null) {
	                    result.close();
	                }

	                if (stmt != null) {
	                    stmt.close();
	                }
	            } catch (SQLException e) {
	            }
	        }

	        return lo;
	    }
	

	public void insert() {
		// TODO Auto-generated method stub
		
	}

	public void delete() {
		// TODO Auto-generated method stub
		
	}

	
	public Activity selectActivity(String activityId) {
			Connection conn = ConnectionManager.getInstance().checkOut();
	        PreparedStatement stmt = null;
	        ResultSet result = null;
	        Activity a = null;
	        String sql = "SELECT * FROM activities WHERE (id = ?) ";

	        try {
	            stmt = conn.prepareStatement(sql);
	            stmt.setString(1, activityId);
	            result = stmt.executeQuery();

	            result.next();
	            a = new Activity();
	            a.setId(result.getString("id"));
                a.setDescription(result.getString("description"));
                a.setIddestination(result.getString("iddestination"));
                a.setMaxnumvolunteers(result.getInt("maxnumvolunteers"));
                a.setMinnumvolunteers(result.getInt("minnumvolunteers"));
                a.setName(result.getString("name"));
                a.setOrganization(result.getString("organization"));
                a.setSignedVolunteers(result.getInt("subscribevolunteers"));
                a.setType(result.getString("type"));   
	            //falta meter los requisitos
	           
	            
	        
	 
	            
	        } catch (SQLException e) {
	            System.out.println("Message: " + e.getMessage());
	            System.out.println("SQLState: " + e.getSQLState());
	            System.out.println("ErrorCode: " + e.getErrorCode());
	        } finally {
	            try {
	                if (result != null) {
	                    result.close();
	                }
	                if (stmt != null) {
	                    stmt.close();
	                }
	            } catch (SQLException e) {
	            }
	        }
	        return a;
	    }
		
	public List<Activity> selectRecentActivities(String destinationId) {
		Connection conn = ConnectionManager.getInstance().checkOut();
        PreparedStatement stmt = null;
        ResultSet result = null;
        List<Activity> searchResults = new LinkedList<Activity>();
        String sql = "SELECT * FROM activities WHERE iddestination = ? ORDER BY id DESC LIMIT 2";

        try {
               	
            stmt = conn.prepareStatement(sql);
        	stmt.setString(1, destinationId);
            result = stmt.executeQuery();

            while (result.next()) {
                Activity a = new Activity();
                a.setId(result.getString("id"));
                a.setDescription(result.getString("description"));
                a.setIddestination(result.getString("iddestination"));
                a.setMaxnumvolunteers(result.getInt("maxnumvolunteers"));
                a.setMinnumvolunteers(result.getInt("minnumvolunteers"));
                a.setName(result.getString("name"));
                a.setOrganization(result.getString("organization"));
                a.setSignedVolunteers(result.getInt("subscribevolunteers"));
                a.setType(result.getString("type"));            
              
                searchResults.add(a);
            }
        } catch (SQLException e) {
            System.out.println("Message: SelectLastProducts: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }

        }
        return searchResults;
    }
	public List<Activity> selectActivitiesFromDestination(String destinationId) {
		 Connection conn = ConnectionManager.getInstance().checkOut();
		 PreparedStatement stmt = null;
	     ResultSet result = null;
        List<Activity> la = new LinkedList<Activity>();
	     String sql = "SELECT * FROM activities WHERE iddestination = ?";

	        try {
	            stmt = conn.prepareStatement(sql);
	            stmt.setString(1, destinationId);
	            result = stmt.executeQuery();

	            while (result.next()) {
	                Activity a = new Activity();
	                a.setId(result.getString("id"));
	                a.setDescription(result.getString("description"));
	                a.setIddestination(result.getString("iddestination"));
	                a.setMaxnumvolunteers(result.getInt("maxnumvolunteers"));
	                a.setMinnumvolunteers(result.getInt("minnumvolunteers"));
	                a.setName(result.getString("name"));
	                a.setOrganization(result.getString("organization"));
	                a.setSignedVolunteers(result.getInt("subscribevolunteers"));
	                a.setType(result.getString("type"));   

	                la.add(a);
	            }
	        } catch (SQLException e) {
	            System.out.println("Message: " + e.getMessage());
	            System.out.println("SQLState: " + e.getSQLState());
	            System.out.println("ErrorCode: " + e.getErrorCode());
	        } finally {
	        	ConnectionManager.getInstance().checkIn(conn);
	            try {
	                if (result != null) {
	                    result.close();
	                }

	                if (stmt != null) {
	                    stmt.close();
	                }
	            } catch (SQLException e) {
	            }
	        }

	        return la;
	    }
	public List<Activity> newActivityFind(List<String> l) {
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        List<Activity> results = new LinkedList<Activity>();
        ResultSet result = null;
        
        String sql = "SELECT * FROM activities WHERE 1 = 1 ";
        if(l.get(0) != null && l.get(0) != ""){
        	sql += " AND name LIKE '%"+l.get(0)+"%' ";
        }
        if(!l.get(1).equals("0")){
        	sql += " AND iddestination = ? ";
        }
        if(!l.get(2).equals("0")){
        	sql += " AND type = ? ";
        }

        try {
            stmt = conn.prepareStatement(sql);
            boolean b = false;
            if(!l.get(1).equals("0")){
            	stmt.setString(1, l.get(1));
            	b = true;
            }
            if(!l.get(2).equals("0")){
            	if(b){
            		stmt.setString(2, l.get(2));
            	}else{
            		stmt.setString(1, l.get(2));
            	}
            }
            stmt.executeQuery();
            result = stmt.executeQuery();

            while (result.next()) {
                Activity a = new Activity();
                System.out.print(a.getName());
                a.setId(result.getString("id"));
                a.setDescription(result.getString("description"));
                a.setIddestination(result.getString("iddestination"));
                a.setMaxnumvolunteers(result.getInt("maxnumvolunteers"));
                a.setMinnumvolunteers(result.getInt("minnumvolunteers"));
                a.setName(result.getString("name"));
                a.setOrganization(result.getString("organization"));
                a.setSignedVolunteers(result.getInt("subscribevolunteers"));
                a.setType(result.getString("type"));
                
                
 
                results.add(a);
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }

        }
        return results;
	}
	
	public List<String> getTypes(){
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        List<String> searchResults = new LinkedList<String>();
        ResultSet result = null;

        try {
            String sql = "SELECT * FROM types";
            stmt = conn.prepareStatement(sql);
            stmt.executeQuery();
            result = stmt.executeQuery();

            while (result.next()) {
                searchResults.add(result.getString("type"));
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }

        }
        return searchResults;
	}
		
}
